﻿ USE ASERDB
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='Proc_GetEaer_Materials' AND TYPE='P')
BEGIN
  DROP PROCEDURE Proc_GetEaer_Materials
END
GO
CREATE Procedure [dbo].[Proc_GetEaer_Materials]
 	@PageIndex int,
	@PageSize int,
	@MaterialsName varchar(100),
	@Department varchar(200),
	@EmergencyType varchar(50),
	@Type varchar(50)
 AS
DECLARE @SQL nvarchar(1000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(200)
IF @PageIndex >1 SET @PageIndex = (@PageIndex-1)*@PageSize +1
--IF @PageIndex = 1  SET @PageIndex =1

SET @Where = ''
IF ((@MaterialsName IS NOT NULL) AND (RTRIM(LTRIM(@MaterialsName))<>''))
BEGIN
  SET @Where = @Where +  ' and charindex('''+@MaterialsName+''',MaterialsName)>0'
END
IF ((@Department IS NOT NULL) AND (RTRIM(LTRIM(@Department))<>''))
BEGIN
  SET @Where = @Where +  ' and charindex('''+@Department+''',Department)>0'
END
IF ((@EmergencyType IS NOT NULL) AND (RTRIM(LTRIM(@EmergencyType))<>''))
BEGIN
  SET @Where = @Where +  ' and EmergencyType = '''+ @EmergencyType + ''''
END
IF ((@Type IS NOT NULL) AND (RTRIM(LTRIM(@Type))<>''))
BEGIN
  SET @Where = @Where +  ' and Type = '''+ @Type + ''''
END
IF ((LEN(@Where)>0) AND (LEFT(@Where,5)=' AND')) 
BEGIN
	SET @Where = Right(@Where,LEN(@Where)-5)
END
SET @SQL = N'SELECT em.MaterialsID,em.MaterialsName,em.Repository,em.Department,em.PrincipalName,em.LinkTel,cc.Name as EmergencyType,em.[Type],em.Quantity,em.MeasureUnit,em.Longitude,em.Latitude,em.Useage,em.Remark,em.BaseFun,em.ControlStep,em.MalfunctionSolve,em.Considerations,em.Size,em.[Weight],em.TypeModel,em.Locality,em.Brand,em.RangeApplication FROM Eaer_Materials em left join CommonCode cc on cc.Code=em.EmergencyType'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'SELECT em.MaterialsID,em.MaterialsName,em.Repository,em.Department,em.PrincipalName,em.LinkTel,cc.Name as EmergencyType,em.[Type],em.Quantity,em.MeasureUnit,em.Longitude,em.Latitude,em.Useage,em.Remark,em.BaseFun,em.ControlStep,em.MalfunctionSolve,em.Considerations,em.Size,em.[Weight],em.TypeModel,em.Locality,em.Brand,em.RangeApplication FROM Eaer_Materials em left join CommonCode cc on cc.Code=em.EmergencyType WHERE ' + @Where + ' ORDER BY MaterialsID DESC'
END
PRINT @SQL
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 32,@PageIndex , @PageSize 
  EXEC sp_cursorclose @P1
END
select @RecordCount
return @@ERROR